using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Reflection;
using Cobalt75.GreyMatter;
using GMID = System.Int64;
using System.Data.SQLite;


namespace Cobalt75.GreyMatter.SQLite
{
    public class SQLiteGMCommunicator : GMCommunicator
    {
        string pPath;
        public string Path
        {
            get
            {
                return pPath;
            }

        }
        public SQLiteGMCommunicator(string database,string databaseFileDirectory)
            : base(database)
        {
             Init("Data Source="+databaseFileDirectory+"\\"+database+";DateTimeFormat=Ticks");
             pPath = databaseFileDirectory;
        }
        /*
        public override string ParameterPrefix
        {
            get
            {
                return ("@");
            }
        }*/
        private void Init(string _ConnectionString)
        {
            //_Database = "gmtest";
            pConnection = new SQLiteConnection();
            pConnection.ConnectionString = _ConnectionString;
            pConnection.Open();
        }
        ~SQLiteGMCommunicator()
        {
            Dispose(true);
        }

        public override IDataParameter CreateParameter(string paraName, object paraValue)
        {
            return new SQLiteParameter(paraName, paraValue);

        }
        protected override GMID GetLastInsertID(IDbCommand command, string tableName)
        {
            command.CommandText = "SELECT last_insert_rowid()";

            return (GMID)Convert.ChangeType(command.ExecuteScalar().ToString(), typeof(GMID));
        }
        public override object DBToCLR(object obj, GMColumnBaseAttribute attr)
        {
            if (attr == null)
            {
                return obj; //For ID lookup
            }
            object v = obj;
            Type t = attr.CLRType;
            if (attr.IsPrimary)
            {
                return v;
            }
            else if (attr.CLRType.IsGenericType)
            {

                Type ta = attr.CLRType.GetGenericTypeDefinition();
                Type tb = typeof(GMRelation<>);

                if (ta == tb && v == DBNull.Value)
                {
                    return ((GMID)0);
                }
                return v;

            }
            else if (t.IsArray && t.IsValueType)
            {
                return v;
            }
            else if (t.IsValueType)
            {
                if (t == typeof(GMID))
                {
                    return v;
                }
                else if (t == typeof(Int16))
                {

                    return v;
                }
                else if (t == typeof(Int32))
                {

                    return v;
                }
                else if (t == typeof(Int64))
                {

                    return v;
                }
                else if (t == typeof(UInt16))
                {

                    return v;
                }
                else if (t == typeof(UInt32))
                {

                    return v;
                }
                else if (t == typeof(UInt64))
                {

                    return v;
                }
                else if (t == typeof(DateTime) && attr.Precision)
                {

                    return new DateTime((long)v);
                }
                else if (t == typeof(DateTime) && !attr.Precision)
                {

                    return v;
                }
                else if (t == typeof(char))
                {

                    return v;
                }
                else if (t == typeof(decimal))
                {

                    return v;
                }
                else if (t == typeof(bool))
                {

                    return v;
                }
                else if (t == typeof(Byte))
                {

                    return v;
                }
                else if (t == typeof(float))
                {

                    return v;
                }
                else if (t == typeof(double))
                {

                    return v;
                }
            }
            else if (t == typeof(string) || t.IsSubclassOf(typeof(string)))
            {
                if (attr.LengthType == GMColumnLength.Small)
                {

                    return v;
                }
                else if (attr.LengthType == GMColumnLength.Medium)
                {

                    return v;
                }
                else if (attr.LengthType == GMColumnLength.Large)
                {

                    return v;
                }
                else if (attr.LengthType == GMColumnLength.Undefined)
                {

                    return v;
                }
                else
                {
                    return v;
                }
            }
            return (v);
        }
        public override object CLRToDB(object obj, GMColumnBaseAttribute attr)
        {
            object v = attr.CLRField.GetValue(obj);
            Type t = attr.CLRType;
            if (attr.IsPrimary)
            {
                return v;
            }
            else if (attr.CLRType.IsGenericType)
            {
                Type ta = attr.CLRType.GetGenericTypeDefinition();
                Type tb = typeof(GMRelation<>);

                if (ta == tb)
                {
                    if (((IGMRelation)v).ID == 0)
                    {
                        return (null);
                    }
                    //return (((GMRelation<GMTable>)v).Value.ID);
                    return ((IGMRelation)v).ID;
                }
                return v;

            }
            else if (t.IsArray && t.IsValueType)
            {
                return v;
            }
            else if (t.IsValueType)
            {
                if (t == typeof(GMID))
                {
                    return v;
                }
                else if (t == typeof(Int16))
                {

                    return v;
                }
                else if (t == typeof(Int32))
                {

                    return v;
                }
                else if (t == typeof(Int64))
                {

                    return v;
                }
                else if (t == typeof(UInt16))
                {

                    return v;
                }
                else if (t == typeof(UInt32))
                {

                    return v;
                }
                else if (t == typeof(UInt64))
                {

                    return v;
                }
                else if (t == typeof(DateTime) && attr.Precision)
                {

                    return ((DateTime)v).Ticks;
                }
                else if (t == typeof(DateTime) && !attr.Precision)
                {

                    return v;
                }
                else if (t == typeof(char))
                {

                    return v;
                }
                else if (t == typeof(decimal))
                {

                    return v;
                }
                else if (t == typeof(bool))
                {

                    return v;
                }
                else if (t == typeof(Byte))
                {

                    return v;
                }
                else if (t == typeof(float))
                {

                    return v;
                }
                else if (t == typeof(double))
                {

                    return v;
                }
            }
            else if (t == typeof(string) || t.IsSubclassOf(typeof(string)))
            {
                if (attr.LengthType == GMColumnLength.Small)
                {

                    return v;
                }
                else if (attr.LengthType == GMColumnLength.Medium)
                {

                    return v;
                }
                else if (attr.LengthType == GMColumnLength.Large)
                {

                    return v;
                }
                else if (attr.LengthType == GMColumnLength.Undefined)
                {

                    return v;
                }
                else
                {
                    return v;
                }
            }
            return (v);
        }
        protected override void SetDatabaseSchemaField(Type t, ref GMColumnBaseAttribute attr)
        {
            if (attr.IsPrimary)
            {
                attr.SetDatabase("INTEGER", "0");
                return;
            }
            else if (attr.CLRType.IsGenericType)
            {
                Type ta = attr.CLRType.GetGenericTypeDefinition();
                Type tb = typeof(GMRelation<>);

                if (ta == tb)
                {
                    attr.SetDatabase("INTEGER", "0");
                    return;
                }
                attr.SetDatabase("?", "?");
                return;

            }
            else if (t.IsArray && t.IsValueType)
            {
                attr.SetDatabase("?", "?");
                return;
            }
            else if (t.IsValueType)
            {
                if (t == typeof(GMID))
                {
                    attr.SetDatabase("INTEGER", "0");
                    return;
                }
                else if (t == typeof(Int16))
                {

                    attr.SetDatabase("INTEGER", "0");
                    return;
                }
                else if (t == typeof(Int32))
                {

                    attr.SetDatabase("INTEGER", "0");
                    return;
                }
                else if (t == typeof(Int64))
                {

                    attr.SetDatabase("INTEGER", "0");
                    return;
                }
                else if (t == typeof(UInt16))
                {

                    attr.SetDatabase("INTEGER", "0");
                    return;
                }
                else if (t == typeof(UInt32))
                {

                    attr.SetDatabase("INTEGER", "0");
                    return;
                }
                else if (t == typeof(UInt64))
                {

                    attr.SetDatabase("INTEGER", "0");
                    return;
                }
                else if (t == typeof(DateTime) && attr.Precision)
                {

                    attr.SetDatabase("INTEGER", "0");
                    return;
                }
                else if (t == typeof(DateTime) && !attr.Precision)
                {

                    attr.SetDatabase("INTEGER", "0");
                    return;
                }
                else if (t == typeof(char))
                {

                    attr.SetDatabase("TEXT", "");
                    return;
                }
                else if (t == typeof(decimal))
                {

                    attr.SetDatabase("REAL", "0");
                    return;
                }
                else if (t == typeof(bool))
                {

                    attr.SetDatabase("INTEGER", "0");
                    return;
                }
                else if (t == typeof(Byte))
                {

                    attr.SetDatabase("INTEGER", "0");
                    return;
                }
                else if (t == typeof(float))
                {

                    attr.SetDatabase("REAL", "0");
                    return;
                }
                else if (t == typeof(double))
                {

                    attr.SetDatabase("REAL", "0");
                    return;
                }
            }
            else if (t == typeof(string) || t.IsSubclassOf(typeof(string)))
            {
                if (attr.LengthType == GMColumnLength.Small)
                {

                    attr.SetDatabase("TEXT", null);
                    return;
                }
                else if (attr.LengthType == GMColumnLength.Medium)
                {

                    attr.SetDatabase("TEXT", null);
                    return;
                }
                else if (attr.LengthType == GMColumnLength.Large)
                {

                    attr.SetDatabase("TEXT", null);
                    return;
                }
                else if (attr.LengthType == GMColumnLength.Undefined)
                {

                    attr.SetDatabase("TEXT", null);
                    return;
                }
                else
                {
                    attr.SetDatabase("TEXT", "");

                    return;
                }
            }
            attr.SetDatabase("BLOB", "");
            return;
        }

        public override string TempIDTableCreationSQL()
        {
            return (String.Format(@"
DROP TABLE IF EXISTS `tbl_ids`;
CREATE TABLE  `tbl_ids` (
  `col` INTEGER NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
"));
        }
        public override void CreateDBSchema()
        {
            Console.WriteLine("DB Schema (designed for SQLite3+)");
            //First create the main tables
            //(In order of priority)
            SortedDictionary<int, GMTableAttribute> SORTtabAttrs = new SortedDictionary<int, GMTableAttribute>();

            foreach (KeyValuePair<Type, GMTableAttribute> kp in pReflectionCache)
            {
                SORTtabAttrs.Add(kp.Value.Priority, kp.Value);
            }


            StringBuilder sB = new StringBuilder();

            //SQLiteConnection.CreateFile(pDatabaseFileDirectory + "\\" + pDatabase);


            sB.AppendFormat(@"
DROP TABLE IF EXISTS `tablesync`;

CREATE TABLE `tablesync` (
  `tablename` varchar(255) NOT NULL default '',
  `modifiedticks` INTEGER NOT NULL default '00000000000000000000',
  PRIMARY KEY  (`tablename`)
);

", Database);



            foreach (GMTableAttribute tableAttr in SORTtabAttrs.Values)
            {
                sB.AppendFormat(@"
INSERT OR IGNORE INTO `tablesync` VALUES ('{1}','0');
", Database, tableAttr.TableName);
                sB.AppendFormat(@"
DROP TABLE IF EXISTS `{1}`;
CREATE TABLE  `{1}` (", Database, tableAttr.TableName);
                foreach (KeyValuePair<FieldInfo, GMColumnBaseAttribute> kp in tableAttr.Columns)
                {
                    if (kp.Value.IsDatabasedColumn)
                    {
                        sB.AppendFormat(@"
    `{0}` {1} {2} {3},", kp.Value.ColumnName, kp.Value.DatabaseTypeID, (kp.Value.AllowNull ? "" : "NOT NULL"), (kp.Value.IsPrimary ? "PRIMARY KEY AUTOINCREMENT" : (kp.Value.AllowNull ? "DEFAULT NULL" : (kp.Value.DatabaseDefaultValue != null) ? "DEFAULT '" + kp.Value.DatabaseDefaultValue + "'" : "")));
                    }
                }
                /*
                foreach (KeyValuePair<FieldInfo, GMColumnAttributeBase> kp in tableAttr.Columns)
                {
                    if (kp.Value.Key)
                    {
                        sB.AppendFormat(@"
    KEY `key_{0}_{1}` (`{1}`),", tableAttr.TableName, kp.Value.ColumnName);

                    }
                }
                foreach (KeyValuePair<FieldInfo, GMColumnAttributeBase> kp in tableAttr.Columns)
                {
                    if (kp.Value.GMRelationType != null && kp.Value.IsDatabasedColumn)
                    {
                        sB.AppendFormat(@"
    CONSTRAINT `{0}_fkr_{1}`
        FOREIGN KEY (`{1}_id`) REFERENCES `{1}` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,", tableAttr.TableName, kp.Value.GMRelationAttr.TableName);

                    }
                }*/
                sB.Remove(sB.Length - 1, 1);
                 
                /*
  `id` int(10) NOT NULL auto_increment,
  `datecreated` INTEGER NOT NULL default '0',
  `datemodified` INTEGER NOT NULL default '0',
  `styles_id` int(10) NOT NULL default '0000000000',
  PRIMARY KEY  (`id`),
  KEY `plus_ibfk_styles` (`styles_id`),
  CONSTRAINT `plus_ibfk_styles` FOREIGN KEY (`styles_id`) REFERENCES `styles` (`id`) ON UPDATE RESTRICT,
  CONSTRAINT `plus_ibfk_styles` FOREIGN KEY (`styles_id`) REFERENCES `styles` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
",);
                 */

                sB.AppendFormat(@"
);
");



            }


            //Then create the link tables

            foreach (GMLinkTable ltbl in pLinkTables)
            {
                sB.AppendFormat(@"
INSERT OR IGNORE INTO `tablesync` VALUES ('{1}','0');
", Database, ltbl.Name);
                sB.AppendFormat(@"
DROP TABLE IF EXISTS `{1}`;
CREATE TABLE  `{1}` (", Database, ltbl.Name);

                sB.AppendFormat(@"
    `{0}_id` INTEGER NOT NULL DEFAULT 0,", ltbl.Master.TableName);
                sB.AppendFormat(@"
    `{0}_id` INTEGER NOT NULL DEFAULT 0,", ltbl.Relation.TableName);
                if (ltbl.Index != null)
                {
                    sB.AppendFormat(@"
    `{0}_id` INTEGER NOT NULL DEFAULT 0,", ltbl.Index.TableName);
                }
                if (ltbl.Parameter != null)
                {

                    sB.AppendFormat(@"
    `{0}` {1},", ltbl.Parameter.ColumnName, ltbl.Parameter.DatabaseTypeID);
                }
                /*
                if (ltbl.Unique && ltbl.Index != null) //Only one relation per index.
                {
                    sB.AppendFormat(@"
    PRIMARY KEY (`{0}`,`{1}`),", ltbl.Master.TableName + "_id", ltbl.Index.TableName + "_id");

                }
                else if (!ltbl.Duplicates) //More than one of the same relation with the same index.
                {
                    if (ltbl.Index != null)
                    {
                        sB.AppendFormat(@"
    PRIMARY KEY (`{0}`,`{1}`,`{2}`),", ltbl.Master.TableName + "_id", ltbl.Relation.TableName + "_id", ltbl.Index.TableName + "_id");
                    }
                    else
                    {
                        sB.AppendFormat(@"
    PRIMARY KEY (`{0}`,`{1}`),", ltbl.Master.TableName + "_id", ltbl.Relation.TableName + "_id");

                    }
                }
                string fkeylinktblname = ltbl.Name;
                if (fkeylinktblname.Length > 32)
                {
                    fkeylinktblname = "";
                    foreach (string str in ltbl.Name.Split('_'))
                    {
                        fkeylinktblname += str.Substring(0, 1);

                    }
                }
                sB.AppendFormat(@"
    CONSTRAINT `{0}_fkr_{1}`
        FOREIGN KEY (`{1}_id`) REFERENCES `{1}` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,", fkeylinktblname, ltbl.Master.TableName);
                sB.AppendFormat(@"
    CONSTRAINT `{0}_fkr_{1}`
        FOREIGN KEY (`{1}_id`) REFERENCES `{1}` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,", fkeylinktblname, ltbl.Relation.TableName);
                if (ltbl.Index != null)
                {

                    sB.AppendFormat(@"
    CONSTRAINT `{0}_fkr_{1}`
        FOREIGN KEY (`{1}_id`) REFERENCES `{1}` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,", fkeylinktblname, ltbl.Index.TableName);
                }
*/
                sB.Remove(sB.Length - 1, 1);
                sB.AppendFormat(@"
);
");

            }


            Console.WriteLine(sB.ToString());
            IDbCommand com = pConnection.CreateCommand();
            com.Connection = pConnection;
            com.CommandText = sB.ToString();
            com.ExecuteNonQuery();

        }
    }
}
